﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SunshineAirlines
{
    public partial class User_Management : Form
    {
        public User_Management()
        {
            InitializeComponent();
        }

         int pageSize = 10;      //每页记录数
         int prePage = 0;    //初始页

        private void button1_Click(object sender, EventArgs e)
        {
            comboBox2.Items.Clear();//caution
            //prePage = 0;
            int currentPage = prePage / 10 + 1;//当前页
            comboBox2.Text = currentPage.ToString();
            if (comboBox1.Text == "All")//sql语句共三种 复制即可
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
                //下拉框填充
                string sql2 = "select count(*) from Users";   
                for (int i = 1; i <=(int)DBHelper.ExecuteScalar(sql2)/10 ; i++)
                { comboBox2.Items.Add(i); }
                label4.Text = "Total Pages:" + ((int)DBHelper.ExecuteScalar(sql2) / 10 ).ToString() + "";
                label5.Text = "Total Recodes:" + DBHelper.ExecuteScalar(sql2).ToString() + "";

            }
            if (comboBox1.Text == "Office User")
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and Users.RoleId='1' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where Users.RoleId='1' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
                //下拉框填充
                string sql2 = "select count(*) from Users where RoleId=1";
                for (int i = 1; i <= (int)DBHelper.ExecuteScalar(sql2) / 10+1; i++)//注意加1
                { comboBox2.Items.Add(i); }
                label4.Text = "Total Pages:" + ((int)DBHelper.ExecuteScalar(sql2) / 10+1).ToString() + "";
                label5.Text = "Total Recodes:" + DBHelper.ExecuteScalar(sql2).ToString() + "";

            }
            if (comboBox1.Text == "Administrator")
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and Users.RoleId='2' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where Users.RoleId='2' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
                //下拉框填充
                string sql2 = "select count(*) from Users where RoleId=2";
                for (int i = 1; i <= (int)DBHelper.ExecuteScalar(sql2) / 10; i++)
                { comboBox2.Items.Add(i); }
                label4.Text = "Total Pages:" + ((int)DBHelper.ExecuteScalar(sql2) / 10 ).ToString() + "";
                label5.Text = "Total Recodes:"+DBHelper.ExecuteScalar(sql2).ToString()+"";
            }

        }

        private void pictureBox1_Click(object sender, EventArgs e)
        {
            prePage = prePage + 10;
            int currentPage = prePage / 10 + 1;//当前页
            comboBox2.Text = currentPage.ToString();
            if (comboBox1.Text == "All")//sql语句共三种 复制即可
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);

            }
            if (comboBox1.Text == "Office User")
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and Users.RoleId='1' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where Users.RoleId='1' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }
            if (comboBox1.Text == "Administrator")
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and Users.RoleId='2' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where Users.RoleId='2' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }
        }

        private void pictureBox4_Click(object sender, EventArgs e)
        {
            prePage = prePage - 10;
            int currentPage = prePage / 10 + 1;//当前页
            comboBox2.Text = currentPage.ToString();
            if (comboBox1.Text == "All")//sql语句共三种 复制即可
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);

            }
            if (comboBox1.Text == "Office User")
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and Users.RoleId='1' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where Users.RoleId='1' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }
            if (comboBox1.Text == "Administrator")
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and Users.RoleId='2' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where Users.RoleId='2' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }


        }



        private void pictureBox3_Click(object sender, EventArgs e)
        {
            prePage = 0;
            int currentPage = prePage / 10 + 1;//当前页
            comboBox2.Text = currentPage.ToString();
            if (comboBox1.Text == "All")//sql语句共三种 复制即可
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);

            }
            if (comboBox1.Text == "Office User")
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and Users.RoleId='1' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where Users.RoleId='1' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }
            if (comboBox1.Text == "Administrator")
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and Users.RoleId='2' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where Users.RoleId='2' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }

        }

        private void pictureBox2_Click(object sender, EventArgs e)
        {
            prePage = 90;
            int currentPage = prePage / 10 + 1;//当前页
            comboBox2.Text = currentPage.ToString();
            if (comboBox1.Text == "All")//sql语句共三种 复制即可
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);

            }
            if (comboBox1.Text == "Office User")
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and Users.RoleId='1' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where Users.RoleId='1' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }


        }

        private void comboBox2_SelectionChangeCommitted(object sender, EventArgs e)
        {
            prePage = (int)comboBox2.SelectedItem * 10-10;//注意-10
            if (comboBox1.Text == "All")//sql语句共三种 复制即可
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);

            }
            if (comboBox1.Text == "Office User")
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and Users.RoleId='1' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where Users.RoleId='1' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }
            if (comboBox1.Text == "Administrator")
            {
                string sql = "select top " + pageSize + " Email,FirstName + ' ' + LastName as Name,Gender,DateOfBirth,Phone,RoleName from Users,Role where Role.RoldId=Users.RoleId and Users.RoleId='2' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%' and UserId not in (select top " + prePage + " UserId from Users where Users.RoleId='2' and FirstName+' ' + LastName like '%" + textBox1.Text.Trim() + "%')";
                this.dataGridView1.DataSource = DBHelper.ExecuteTable(sql);
            }

        }

        private void button3_Click(object sender, EventArgs e)
        {
            AddEdit_User f1 = new AddEdit_User();
            f1.Show();
            
        }

        private void User_Management_Load(object sender, EventArgs e)
        {

        }

        private void button2_Click(object sender, EventArgs e)
        {
            AddEdit_User f1 = new AddEdit_User();
            f1.Show();
        }
    }
}
